﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
                public class BuildDAL
    {
                                                        public static DataTable p_build_listall_table(int eid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@eid", eid));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_listall", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

        public static DataTable p_build_listall_table(int eid, _Database db,int type)
        {
            string sql = "select * from t_build where delFlag=0 and eid = "+eid+" order by id ";
            DataSet ds= db.Query(sql);

            return ds.Tables[0];
        }

                                                                                public static DataTable p_build_list_table(int page, int pagesize, int eid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@eid", eid));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                public static DataTable p_build_list_condition_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_list_condition", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                public static DataTable p_build_list_table(int top, string condition, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                string sql = "select top " + top + " id, name, othername from t_estate " + condition;
                               
                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }
                                                                public static DataTable p_build_newlist_table(int top, string condition, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                string sql = "select top " + top + " id, finalName as name, othername from t_build   " + condition;

                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }
                                                                        public static DataTable p_build_list_table2(int top, string condition, string condition2, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                string sql = "select top 10 id,name, name as finalname,0 as floorTotal,id as eid,name as ename,roadName, otherName, 0 as [level] from t_estate " + condition2 + " union all ";
                sql += "select top " + top + " id, name, finalname, floorTotal, eid, ename, roadName, othername, [level] from v_build " + condition;

                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                public static DataTable GetBuildByName(string name, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                string sql = "select top 1 id,name, name as finalname,0 as floorTotal,id as eid,name as ename,roadName, otherName, 0 as [level] from t_estate where delFlag=0 and isValid=0 and name = '" + name + "' union all ";
                sql += "select top 1 id, name, finalname, floorTotal, eid, ename, roadName, othername, [level] from v_build where delFlag=0 and isValid=0 and finalname = '" + name + "'";
                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static DataTable p_Estate_list_table(int top, string condition, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                                string sql = "select top " + top + " id,name from t_estate " + condition;

                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static DataTable p_build_list_table2(string sql, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {                
                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                public static DataTable p_build_list_dg(string sql, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                ds = db.Query(sql);

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }



                                                                                public static DataTable p_build_list_level_table1(int page, int pagesize, int eid, ref int total, _Database db,string where,int hid)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@eid", eid));
            parms.Add(new SqlParameter("@hid", hid));
            parms.Add(new SqlParameter("@where", where));
            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_list_level1", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                                                                                public static DataTable p_build_list_level_table(int page, int pagesize, int eid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@eid", eid));
           
            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_list_level", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
        public static DataTable BuildDetail(int id, _Database db)
        {
            DataSet ds = null;
            try
            {
                string sql = "select * from t_build where id = " + id.ToString();
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            DataTable dt = new DataTable();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                public static DataTable SelectNamesById(int id, _Database db)
        {
            DataSet ds = null;
            try
            {
                string sql = "select * from t_build where eid = " + id.ToString() + " and parentid=0 and delFlag=0 ";
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            DataTable dt = new DataTable();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                public static DataTable SelectNamesByBId(int id, _Database db)
        {
            DataSet ds = null;
            try
            {
                string sql = "select * from t_build where delFlag=0 and parentId=" + id.ToString();
                ds = db.Query(sql);
            }
            catch { }
            finally { db.close(); }

            DataTable dt = new DataTable();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                                                                                                                                        public static void p_build_save(int id, string name, string otherName, string namePy, string otherNamePy, int parentId, string usage, int floorTotal, string finalName, int eid, string ename, string remark, int delFlag, int isValid, string registername, int registerid, string registerdept, int registerdeptid,string type, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@name", name));
            parms.Add(new SqlParameter("@otherName", otherName));
            parms.Add(new SqlParameter("@namePy", namePy));
            parms.Add(new SqlParameter("@otherNamePy", otherNamePy));
            parms.Add(new SqlParameter("@parentId", parentId));
            parms.Add(new SqlParameter("@usage", usage));
            parms.Add(new SqlParameter("@floorTotal", floorTotal));
            parms.Add(new SqlParameter("@finalName", finalName));
            parms.Add(new SqlParameter("@eid", eid));
            parms.Add(new SqlParameter("@ename", ename));
            parms.Add(new SqlParameter("@remark", remark));
            parms.Add(new SqlParameter("@delFlag", delFlag));
            parms.Add(new SqlParameter("@registername", registername));
            parms.Add(new SqlParameter("@registerid", registerid));
            parms.Add(new SqlParameter("@registerdept", registerdept));
            parms.Add(new SqlParameter("@registerdeptid", registerdeptid));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@type", type));
            
            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_build_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                        public static void p_build_delete(int id, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_build_delete", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        
                                                                                                public static int UpdateBName(string id, string oname, string type, string tablename, string commitName, string commitDept, string commitDeptID)
        {
            int row = 0;
            using (_Database db = new _Database("estate"))
            {
                if (type == "")
                {
                    string sql = "update t_build set otherName='" + oname + "' where id=" + id + "";
                    row = db.ExecuteSql(sql);
                }
                else if (type != "")
                {
                    string sql = "insert into t_estate_other_name(type,tableName,tableId,modifyName,commitName,commitDept,commitDeptid,commitDate) values(" + type + ",'" + tablename + "'," + id + ",'" + oname + "','" + commitName + "','" + commitDept + "','" + commitDeptID + "',GETDATE())";
                    row = db.ExecuteSql(sql);
                }
            }

            return row;
        }
        
                public static DataTable p_build_recycle_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_build_recycle_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
        
                                                        public static int DeleteBuild(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "delete from t_build where  id =" + id + " and delflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                        public static int DelBuild(int id, _Database db)
        {
            int rows = 0;

            try
            {
                                string sql = "update t_build set delflag=1 where  id =" + id + "; update t_roomno set delflag=1 where bid=" + id;
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                                                        public static int DelBuildAndIsValid(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_build set delflag=1 where  id =" + id +" and isvalid>0; update t_roomno set delflag=1 where isValid=1 and bid=" + id;
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                        public static int SHBuild(string sql, _Database db)
        {
            int rows = 0;

            try
            {
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                public static int UpdateDelFlag(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update  t_build set delflag=0  where  id =" + id + " and delflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                public static int BuildCheck(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_build set isValid = 0 where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int BuildCheck(int id,int isvalid,string Approval_views, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_build set isValid = " + isvalid + ",Approvalviews='"+Approval_views+"' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                                public static DataTable p_v_build_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_build_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static int SelectCountByParentId(int id, _Database db) 
        {
            int count = 0;
            string sql = " select count(*) from t_build where parentId=" + id + "  and delFlag=0 ";
            DataSet ds = db.Query(sql);
            if (ds.Tables[0].Rows.Count > 0) 
            {
                count =_Convert.ToInt(ds.Tables[0].Rows[0][0].ToString());

            }
            return count;
        }

                public static string UpdateParentIdById(int id,int parentId,_Database db)
        {
            int count = 0;
            string outmsg = "操作失败！";
            string sql = " update t_build set parentId="+parentId+" where id=" + id + " ";
            sql += " ;insert into t_tran_log(user_id,user_name,user_from,user_from_name,user_to,user_to_name,opr_type,sqlText)values(" + _Utility.GetInfoBySession().m_sUserID + ",'" + _Utility.GetInfoBySession().m_sUserName + "','0','0','0','0','移动栋阁','" + sql + "')";

            count = db.ExecuteSql(sql);

            if (count > 0)
            {
                outmsg = "操作成功！";
            }
            return outmsg;
        }
        
                                                                public static string RemoveRoomNoByBid(int id, int bid, _Database db)
        {
            int count = 0;
            string outmsg = "操作失败！";
            string sql = " update t_roomno   set  bid="+bid+",bname=finalName from t_build where  t_build.id="+bid+" and t_roomno.id="+id+"  ";
            sql += " ;insert into t_tran_log(user_id,user_name,user_from,user_from_name,user_to,user_to_name,opr_type,sqlText)values("+_Utility.GetInfoBySession().m_sUserID+",'"+_Utility.GetInfoBySession().m_sUserName+"','0','0','0','0','移动房号','"+sql+"')";
            count = db.ExecuteSql(sql);

            if (count > 0)
            {
                outmsg = "操作成功！";
            }
            return outmsg;
        }

                                                                public static string ExistsRoomNo(int id,int bid,_Database db)
        {
            string roomno = "";
            string sql = " select name from t_roomno where name=(select name from t_roomno where id="+id+") and floor=(select floor from t_roomno where id="+id+") and bid= "+bid+"";

            DataSet ds = db.Query(sql);

            if (ds.Tables[0].Rows.Count > 0) 
            {
                roomno =ds.Tables[0].Rows[0][0].ToString();
            }
            return roomno;
        }

       
       
       
    }
}
